{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Congestion Charging - Easy\n",
    "\n",
    "![rel](https://sqlzoo.net/w/images/f/f6/CongestionCharge.png)\n",
    "\n",
    "camera(**id**, perim)\n",
    "\n",
    "keeper(**id**, name, address)\n",
    "\n",
    "vehicle(**id**, keeper)\n",
    "\n",
    "image(**_camera_**, **whn**, reg)\n",
    "\n",
    "permit(**_reg_**, **sDate**, chargeType)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdin",
     "output_type": "stream",
     "text": [
      " ·········\n"
     ]
    }
   ],
   "source": [
    "# Prerequesites\n",
    "import getpass\n",
    "%load_ext sql\n",
    "pwd = getpass.getpass()\n",
    "# %sql mysql+pymysql://root:$pwd@localhost:3306/sqlzoo\n",
    "%sql postgresql://postgres:$pwd@localhost/sqlzoo\n",
    "%config SqlMagic.displaylimit = 20"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Sample query\n",
    "\n",
    "List the vehicles for which 'Strenuous, Sam' is the registered keeper. The link between Keepers and Vehicles is via the foreign key specified in the CREATE TABLE vehicle statement. Note the line:\n",
    "\n",
    "```\n",
    " ,FOREIGN KEY(keeper) REFERENCES keeper(id)\n",
    "```\n",
    "\n",
    "This will be the basis of our join condition."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1.\n",
    "Show the name and address of the keeper of vehicle SO 02 PSP."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/sqlzoo\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>name</th>\n",
       "        <th>address</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Strenuous, Sam</td>\n",
       "        <td>Surjection Street</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Strenuous, Sam', 'Surjection Street')]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT name, address FROM\n",
    "  keeper JOIN vehicle ON (keeper.id=vehicle.keeper)\n",
    "    WHERE vehicle.id='SO 02 PSP';"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2.\n",
    "Show the number of cameras that take images for incoming vehicles."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/sqlzoo\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>count</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>8</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(8,)]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT COUNT(*) FROM camera\n",
    "  WHERE perim='IN';"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3.\n",
    "List the image details taken by Camera 10 before 26 Feb 2007."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/sqlzoo\n",
      "48 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>camera</th>\n",
       "        <th>whn</th>\n",
       "        <th>reg</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>2007-02-25 06:10:13</td>\n",
       "        <td>SO 02 ASP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>9</td>\n",
       "        <td>2007-02-25 06:26:04</td>\n",
       "        <td>SO 02 ASP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>17</td>\n",
       "        <td>2007-02-25 06:20:01</td>\n",
       "        <td>SO 02 ASP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>18</td>\n",
       "        <td>2007-02-25 06:23:40</td>\n",
       "        <td>SO 02 ASP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>8</td>\n",
       "        <td>2007-02-25 07:35:41</td>\n",
       "        <td>SO 02 CSP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>8</td>\n",
       "        <td>2007-02-25 07:48:10</td>\n",
       "        <td>SO 02 CSP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>10</td>\n",
       "        <td>2007-02-25 07:45:11</td>\n",
       "        <td>SO 02 CSP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>11</td>\n",
       "        <td>2007-02-25 07:58:01</td>\n",
       "        <td>SO 02 CSP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>12</td>\n",
       "        <td>2007-02-25 07:04:31</td>\n",
       "        <td>SO 02 CSP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>17</td>\n",
       "        <td>2007-02-25 06:57:31</td>\n",
       "        <td>SO 02 CSP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>17</td>\n",
       "        <td>2007-02-25 07:00:40</td>\n",
       "        <td>SO 02 CSP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>18</td>\n",
       "        <td>2007-02-25 07:39:04</td>\n",
       "        <td>SO 02 CSP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>18</td>\n",
       "        <td>2007-02-25 07:42:30</td>\n",
       "        <td>SO 02 CSP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>18</td>\n",
       "        <td>2007-02-25 07:55:11</td>\n",
       "        <td>SO 02 CSP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>19</td>\n",
       "        <td>2007-02-25 07:51:10</td>\n",
       "        <td>SO 02 CSP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>9</td>\n",
       "        <td>2007-02-25 18:54:30</td>\n",
       "        <td>SO 02 DSP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>18</td>\n",
       "        <td>2007-02-25 16:29:11</td>\n",
       "        <td>SO 02 DSP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>19</td>\n",
       "        <td>2007-02-25 16:31:01</td>\n",
       "        <td>SO 02 DSP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>19</td>\n",
       "        <td>2007-02-25 17:42:41</td>\n",
       "        <td>SO 02 DSP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>3</td>\n",
       "        <td>2007-02-25 17:16:11</td>\n",
       "        <td>SO 02 ESP</td>\n",
       "    </tr>\n",
       "</table>\n",
       "<span style=\"font-style:italic;text-align:center;\">48 rows, truncated to displaylimit of 20</span>"
      ],
      "text/plain": [
       "[(1, datetime.datetime(2007, 2, 25, 6, 10, 13), 'SO 02 ASP'),\n",
       " (9, datetime.datetime(2007, 2, 25, 6, 26, 4), 'SO 02 ASP'),\n",
       " (17, datetime.datetime(2007, 2, 25, 6, 20, 1), 'SO 02 ASP'),\n",
       " (18, datetime.datetime(2007, 2, 25, 6, 23, 40), 'SO 02 ASP'),\n",
       " (8, datetime.datetime(2007, 2, 25, 7, 35, 41), 'SO 02 CSP'),\n",
       " (8, datetime.datetime(2007, 2, 25, 7, 48, 10), 'SO 02 CSP'),\n",
       " (10, datetime.datetime(2007, 2, 25, 7, 45, 11), 'SO 02 CSP'),\n",
       " (11, datetime.datetime(2007, 2, 25, 7, 58, 1), 'SO 02 CSP'),\n",
       " (12, datetime.datetime(2007, 2, 25, 7, 4, 31), 'SO 02 CSP'),\n",
       " (17, datetime.datetime(2007, 2, 25, 6, 57, 31), 'SO 02 CSP'),\n",
       " (17, datetime.datetime(2007, 2, 25, 7, 0, 40), 'SO 02 CSP'),\n",
       " (18, datetime.datetime(2007, 2, 25, 7, 39, 4), 'SO 02 CSP'),\n",
       " (18, datetime.datetime(2007, 2, 25, 7, 42, 30), 'SO 02 CSP'),\n",
       " (18, datetime.datetime(2007, 2, 25, 7, 55, 11), 'SO 02 CSP'),\n",
       " (19, datetime.datetime(2007, 2, 25, 7, 51, 10), 'SO 02 CSP'),\n",
       " (9, datetime.datetime(2007, 2, 25, 18, 54, 30), 'SO 02 DSP'),\n",
       " (18, datetime.datetime(2007, 2, 25, 16, 29, 11), 'SO 02 DSP'),\n",
       " (19, datetime.datetime(2007, 2, 25, 16, 31, 1), 'SO 02 DSP'),\n",
       " (19, datetime.datetime(2007, 2, 25, 17, 42, 41), 'SO 02 DSP'),\n",
       " (3, datetime.datetime(2007, 2, 25, 17, 16, 11), 'SO 02 ESP'),\n",
       " (10, datetime.datetime(2007, 2, 25, 18, 8, 40), 'SO 02 ESP'),\n",
       " (11, datetime.datetime(2007, 2, 25, 18, 8), 'SO 02 FSP'),\n",
       " (5, datetime.datetime(2007, 2, 25, 7, 10), 'SO 02 GSP'),\n",
       " (12, datetime.datetime(2007, 2, 25, 18, 8, 13), 'SO 02 GSP'),\n",
       " (16, datetime.datetime(2007, 2, 25, 7, 13), 'SO 02 GSP'),\n",
       " (9, datetime.datetime(2007, 2, 25, 16, 45, 4), 'SO 02 HSP'),\n",
       " (9, datetime.datetime(2007, 2, 25, 16, 48, 11), 'SO 02 HSP'),\n",
       " (9, datetime.datetime(2007, 2, 25, 16, 51, 30), 'SO 02 HSP'),\n",
       " (9, datetime.datetime(2007, 2, 25, 16, 58, 1), 'SO 02 ISP'),\n",
       " (12, datetime.datetime(2007, 2, 25, 17, 1, 13), 'SO 02 ISP'),\n",
       " (3, datetime.datetime(2007, 2, 25, 17, 7), 'SO 02 JSP'),\n",
       " (3, datetime.datetime(2007, 2, 25, 17, 17, 3), 'SO 02 JSP'),\n",
       " (18, datetime.datetime(2007, 2, 25, 17, 10, 43), 'SO 02 JSP'),\n",
       " (19, datetime.datetime(2007, 2, 25, 17, 14, 11), 'SO 02 JSP'),\n",
       " (10, datetime.datetime(2007, 2, 25, 18, 23, 11), 'SO 02 MUP'),\n",
       " (11, datetime.datetime(2007, 2, 25, 18, 26, 13), 'SO 02 NUP'),\n",
       " (12, datetime.datetime(2007, 2, 25, 18, 29, 1), 'SO 02 OUP'),\n",
       " (3, datetime.datetime(2007, 2, 25, 18, 33, 10), 'SO 02 PUP'),\n",
       " (3, datetime.datetime(2007, 2, 25, 18, 39, 10), 'SO 02 PUP'),\n",
       " (15, datetime.datetime(2007, 2, 25, 18, 36, 31), 'SO 02 PUP'),\n",
       " (9, datetime.datetime(2007, 2, 25, 16, 39, 10), 'SO 02 RSP'),\n",
       " (18, datetime.datetime(2007, 2, 25, 16, 38, 31), 'SO 02 RSP'),\n",
       " (9, datetime.datetime(2007, 2, 25, 16, 31, 1), 'SO 02 SSP'),\n",
       " (18, datetime.datetime(2007, 2, 25, 16, 28, 40), 'SO 02 SSP'),\n",
       " (2, datetime.datetime(2007, 2, 25, 7, 20, 1), 'SO 02 TSP'),\n",
       " (19, datetime.datetime(2007, 2, 25, 7, 23), 'SO 02 TSP'),\n",
       " (19, datetime.datetime(2007, 2, 25, 7, 26, 31), 'SO 02 TSP'),\n",
       " (19, datetime.datetime(2007, 2, 25, 7, 29), 'SO 02 TSP')]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT image.* FROM\n",
    "  image JOIN camera ON (image.camera=camera.id)\n",
    "    WHERE whn < '2007-02-26'::DATE;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 4.\n",
    "List the number of images taken by each camera. Your answer should show how many images have been taken by camera 1, camera 2 etc. The list must NOT include the images taken by camera 15, 16, 17, 18 and 19."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/sqlzoo\n",
      "9 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>concat</th>\n",
       "        <th>count</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Camera 1</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Camera 2</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Camera 3</td>\n",
       "        <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Camera 5</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Camera 8</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Camera 9</td>\n",
       "        <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Camera 10</td>\n",
       "        <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Camera 11</td>\n",
       "        <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Camera 12</td>\n",
       "        <td>4</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Camera 1', 1),\n",
       " ('Camera 2', 1),\n",
       " ('Camera 3', 5),\n",
       " ('Camera 5', 1),\n",
       " ('Camera 8', 2),\n",
       " ('Camera 9', 8),\n",
       " ('Camera 10', 4),\n",
       " ('Camera 11', 3),\n",
       " ('Camera 12', 4)]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT CONCAT('Camera ', camera), COUNT(*)\n",
    "  FROM image\n",
    "    WHERE camera NOT BETWEEN 15 AND 19\n",
    "    GROUP BY camera\n",
    "    ORDER BY camera;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 5.\n",
    "A number of vehicles have permits that start on 30th Jan 2007. List the name and address for each keeper in alphabetical order without duplication."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/sqlzoo\n",
      "4 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>name</th>\n",
       "        <th>address</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Ambiguous, Arthur</td>\n",
       "        <td>Absorption Ave.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Assiduous, Annie</td>\n",
       "        <td>Attribution Alley</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Contiguous, Carol</td>\n",
       "        <td>Circumscription Close</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Strenuous, Sam</td>\n",
       "        <td>Surjection Street</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Ambiguous, Arthur', 'Absorption Ave.'),\n",
       " ('Assiduous, Annie', 'Attribution Alley'),\n",
       " ('Contiguous, Carol', 'Circumscription Close'),\n",
       " ('Strenuous, Sam', 'Surjection Street')]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT DISTINCT name, address\n",
    "  FROM keeper JOIN vehicle ON (keeper.id=vehicle.keeper) JOIN\n",
    "    permit ON (permit.reg=vehicle.id)\n",
    "    WHERE sdate='2007-1-30'::DATE\n",
    "    ORDER BY name;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
